﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ENT;

namespace DAT
{
    public class datDiem
    {
        SqlCommand cmd;
        SqlDataReader dr;
        public List<entDiem> getDiemHocKy(string nam, string hk, string masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_DiemGetByHocKy", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[3];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[1] = new SqlParameter("namhoc", DbType.String);
            sqla[2] = new SqlParameter("hocky", DbType.String);

            sqla[0].Value = masv;
            sqla[1].Value = nam;
            sqla[2].Value = hk;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entDiem entD;
            List<entDiem> lst = new List<entDiem>();
            while (dr.Read())
            {
                entD = new entDiem();
                entD.MaMon = dr["mamon"].ToString();
                entD.MaSinhVien = dr["masinhvien"].ToString();
                entD.Diem = dr["diem"].ToString();
                entD.TenMon = dr["tenmon"].ToString();
                entD.SoTC = dr["sotc"].ToString();
                lst.Add(entD);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }
        public List<entDiem> getDiemTBHocKy(string nam, string hk, string masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_DiemGetTBHocKy", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[3];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[1] = new SqlParameter("nam", DbType.Int32);
            sqla[2] = new SqlParameter("hocky", DbType.Int32);

            sqla[0].Value = masv;
            sqla[1].Value = nam;
            sqla[2].Value = hk;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entDiem entD;
            List<entDiem> lst = new List<entDiem>();
            while (dr.Read())
            {
                entD = new entDiem();
                entD.MaSinhVien = dr["masinhvien"].ToString();
                entD.Diem = dr["TBHK"].ToString();
                lst.Add(entD);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }
        public List<entDiem> getDiemTBTL(string masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_DiemGetTBTL", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            

            sqla[0].Value = masv;
           

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entDiem entD;
            List<entDiem> lst = new List<entDiem>();
            while (dr.Read())
            {
                entD = new entDiem();
                entD.MaSinhVien = dr["masinhvien"].ToString();
                entD.Diem = dr["TBTL"].ToString();
                lst.Add(entD);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }

        public List<entDiem> getNamHocHocKy(string masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_DiemGetNamHocHocKyByMasv", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
         
            sqla[0].Value = masv;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entDiem entD;
            List<entDiem> lst = new List<entDiem>();
            while (dr.Read())
            {
                entD = new entDiem();
                entD.NamHoc = dr["namhoc"].ToString();
                entD.HocKy = dr["hocky"].ToString();
                lst.Add(entD);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }
    }
}
